<?php
Class ModelreportV2supplier extends Model {

    // public function getInTotal(){
    //     $sql = "SELECT SUM(sd.product_quantity * sd.product_price ) AS intotal, p.product_class1, cd.`name`, vs.vendor_name, vs.vendor_id FROM stock_in_detail AS sd LEFT JOIN product_option_value AS po ON sd.product_code = po.product_code LEFT JOIN product AS p ON po.product_id = p.product_id LEFT JOIN vendor AS v ON p.product_id = v.vproduct_id LEFT JOIN vendors AS vs ON v.vendor = vs.vendor_id LEFT JOIN category_description AS cd ON p.product_class1 = cd.category_id WHERE sd.`in_id` IN (SELECT in_id FROM stock_in WHERE STATUS =2 ) GROUP BY vs.vendor_id";
    //     $query = $this->db->query($sql);
    //     foreach ($query->rows as $row) {
    //         $pclass = $row['product_class1'].$row['vendor_id'];
    //         if (!empty($row['vendor_id'])) {
    //             $ret[$pclass]['intotal'] = max(0, $row['intotal']);
    //             $ret[$pclass]['product_class1'] = $row['product_class1'];
    //             $ret[$pclass]['name'] = $row['name'];
    //             $ret[$pclass]['vendor_name'] = $row['vendor_name'];
    //             $ret[$pclass]['vendor_id'] = $row['vendor_id'];
    //         }
    //     }
    //     return $ret;
    // }
    public function getOutTotal($rp){
        $ret = $rp;

        $date = date('Ym').'0000';
        $dates = date('Y').'0000';
        $sql = "SELECT SUM(OP.`total`)  AS 'outtotal', SUM(VP.`product_cost` * OP.`quantity` ) AS 'buytotal', vs.vendor_name, vs.vendor_id FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN vendors AS vs ON VP.vendor = vs.vendor_id LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND `date_added` <'".$date."000000' AND  `date_added` >'".$dates."000000') GROUP BY vs.vendor_id";
        // echo $sql;die();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['vendor_id'];
            $pcla = $row['vendor_id'];
            if (!empty($row['vendor_id'])) {
                $ret[$pclass]['outtotal'] = max(0, $row['outtotal']);
                $ret[$pclass]['buytotal'] = max(0, $row['buytotal']);
                $ret[0][$pcla]+= max(0, $row['outtotal']);
                $ret[$pclass]['product_class1'] = $row['product_class1'];
                $ret[$pclass]['name'] = $row['name'];
                $ret[$pclass]['vendor_name'] = $row['vendor_name'];
                $ret[$pclass]['vendor_id'] = $row['vendor_id'];
                $ret[$pclass]['lre'] = $ret[$pclass]['outtotal']-$ret[$pclass]['buytotal'];
                $ret[$pclass]['ml'] = (round(($ret[$pclass]['outtotal']-$ret[$pclass]['buytotal'])/$ret[$pclass]['outtotal'], 4)*100).'%';
    
            }
        }
        $week  = date('Ymd', strtotime('-4 week')).'000000';
         $sql = "SELECT SUM(OP.`total`)  AS 'wouttotal', SUM(VP.`product_cost` * OP.`quantity` ) AS 'wbuytotal', vs.vendor_name, vs.vendor_id FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN vendors AS vs ON VP.vendor = vs.vendor_id LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND  `date_added` >'".$week."000000') GROUP BY vs.vendor_id";
        // echo $sql;die();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['vendor_id'];
            $pcla = $row['vendor_id'];
            if (!empty($row['vendor_id'])) {
                $ret[$pclass]['wouttotal'] = max(0, $row['wouttotal']);
                $ret[$pclass]['wbuytotal'] = max(0, $row['wbuytotal']);
    
            }
        }

        return $ret;
    }

    public function getInvTotal($outtotal){
        $ret = $outtotal;
        $sql = "SELECT p.product_class1, cd.`name`, vs.vendor_name, vs.vendor_id, SUM(v.product_cost*i.available_quantity) as 'invtotal'FROM inventory AS i LEFT JOIN product_option_value AS po ON i.product_code = po.product_code LEFT JOIN product AS p ON po.product_id = p.product_id LEFT JOIN vendor AS v ON p.product_id = v.vproduct_id LEFT JOIN vendors AS vs ON v.vendor = vs.vendor_id LEFT JOIN category_description AS cd ON p.product_class1 = cd.category_id WHERE i.`status`!=2 GROUP BY vs.vendor_id";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['vendor_id'];
            if (!empty($row['vendor_id'])) {
                $ret[$pclass]['invtotal'] = max(0, $row['invtotal']);
                $ret[$pclass]['product_class1'] = $row['product_class1'];
                $ret[$pclass]['name'] = $row['name'];
                $ret[$pclass]['vendor_name'] = $row['vendor_name'];
                $ret[$pclass]['vendor_id'] = $row['vendor_id'];
            }
        }
        // var_dump($ret);
        return $ret;
    }


    public function getOut($d){
        $sql = "SELECT SUM(OP.`total`)  AS 'outtotal', SUM(VP.`product_cost` * OP.`quantity` ) AS 'buytotal', vs.vendor_name, vs.vendor_id FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN vendors AS vs ON VP.vendor = vs.vendor_id LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND DATE_FORMAT(`date_added`, '%Y-%m')  ='".$d."') GROUP BY vs.vendor_id";
        // echo $sql;die();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            if (!empty($row['vendor_id'])) {
                $data = array(
                'vendor_id'=>$row['vendor_id'],
                'year_month'=>date('Y-m-d H:i:s',strtotime($d)),
                'sales_money'=>$row['outtotal'],
                'date_added'=>date('Y-m-d H:i:s'),
                );
                 M('vendors_report')->data($data)->add();
                 // die();
            }
            

         }
        
    }

    public function getrp(){
        $sql = "SELECT * FROM `vendors_report`  GROUP BY vendor_id,`year_month`";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['vendor_id'];
            $year_month = date('Y-m',strtotime($row['year_month']));
            if (!empty($row['vendor_id'])) {
                $ret[$pclass][$year_month] = max(0, $row['sales_money']);
    
            }
        }
        // var_dump($ret);die();
        return $ret;
    }


}






